"use strict";
var __importDefault = (this && this.__importDefault) || function (mod) {
    return (mod && mod.__esModule) ? mod : { "default": mod };
};
Object.defineProperty(exports, "__esModule", { value: true });
exports.SqliteDatabase = void 0;
exports.getDbInstance = getDbInstance;
const better_sqlite3_1 = __importDefault(require("better-sqlite3"));
const path_1 = __importDefault(require("path"));
const fs_1 = __importDefault(require("fs"));
const DB_PATH = path_1.default.join(process.cwd(), 'data', 'delivery-management.db');
class SqliteDatabase {
    constructor() {
        this.db = null;
        this.initialized = false;
        this.ensureDataDirectory();
        this.init();
    }
    init() {
        this.db = new better_sqlite3_1.default(DB_PATH);
        this.db.pragma('journal_mode = WAL');
        this.initialized = true;
    }
    ensureInitialized() {
        if (!this.initialized || !this.db || !this.db.open) {
            this.init();
        }
    }
    run(sql, params = []) {
        this.ensureInitialized();
        return this.db.prepare(sql).run(params);
    }
    get(sql, params = []) {
        this.ensureInitialized();
        return this.db.prepare(sql).get(params);
    }
    all(sql, params = []) {
        this.ensureInitialized();
        return this.db.prepare(sql).all(params);
    }
    exec(sql) {
        this.ensureInitialized();
        this.db.exec(sql);
    }
    close() {
        if (this.db && this.db.open) {
            this.db.close();
            this.initialized = false;
        }
    }
    ensureDataDirectory() {
        const dataDir = path_1.default.dirname(DB_PATH);
        if (!fs_1.default.existsSync(dataDir)) {
            fs_1.default.mkdirSync(dataDir, { recursive: true });
        }
    }
    // 初始化数据库结构（异步）
    initializeDatabase() {
        this.ensureInitialized();
        this.exec(`
      CREATE TABLE IF NOT EXISTS devices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        specialty TEXT NOT NULL,
        classification_name TEXT,
        classification_code TEXT,
        naming_table_id TEXT,
        position_table_id TEXT,
        base_params_table_id TEXT,
        tech_spec_table_id TEXT,
        sequence_number TEXT,
        remarks TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
      CREATE TABLE IF NOT EXISTS measurement_units (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        category TEXT NOT NULL,
        unit TEXT NOT NULL,
        main_category TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
      CREATE TABLE IF NOT EXISTS naming_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        parameter_value TEXT,
        data_type TEXT,
        example_description TEXT,
        measurement_class TEXT,
        data_source TEXT,
        is_required BOOLEAN DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE TABLE IF NOT EXISTS position_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        parameter_value TEXT,
        data_type TEXT,
        example_description TEXT,
        measurement_class TEXT,
        data_source TEXT,
        is_required BOOLEAN DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE TABLE IF NOT EXISTS base_parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        sequence TEXT,
        parameter_name TEXT NOT NULL,
        example_description TEXT,
        data_type TEXT,
        measurement_class TEXT,
        data_source TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
      );
      CREATE INDEX IF NOT EXISTS idx_devices_specialty ON devices (specialty);
      CREATE INDEX IF NOT EXISTS idx_measurement_units_category ON measurement_units (category);
      CREATE INDEX IF NOT EXISTS idx_naming_parameters_device_id ON naming_parameters (device_id);
      CREATE INDEX IF NOT EXISTS idx_position_parameters_device_id ON position_parameters (device_id);
      CREATE INDEX IF NOT EXISTS idx_base_parameters_device_id ON base_parameters (device_id);
    `);
    }
    // 插入设备
    insertDevice(device) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const sql = `
      INSERT INTO devices (
        device_id, name, specialty, created_at, updated_at,
        classification_name, classification_code, naming_table_id, position_table_id,
        base_params_table_id, tech_spec_table_id, sequence_number, remarks
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
        const params = [
            device.device_id,
            device.name || '未命名设备',
            device.specialty || '配管专业',
            now,
            now,
            device.classification_name || '',
            device.classification_code || '',
            device.naming_table_id || '',
            device.position_table_id || '',
            device.base_params_table_id || '',
            device.tech_spec_table_id || '',
            device.sequence_number || '',
            device.remarks || ''
        ];
        const result = this.run(sql, params);
        const id = result.lastInsertRowid;
        return {
            ...device,
            id,
            created_at: now,
            updated_at: now
        };
    }
    // 获取所有设备
    getAllDevices() {
        this.ensureInitialized();
        return this.all(`SELECT * FROM devices`);
    }
    // 根据device_id获取设备
    getDeviceByDeviceId(deviceId) {
        this.ensureInitialized();
        const sql = `SELECT * FROM devices WHERE device_id = ?`;
        return this.get(sql, [deviceId]);
    }
    // 根据专业获取设备
    getDevicesBySpecialty(specialty) {
        this.ensureInitialized();
        return this.all(`SELECT * FROM devices WHERE specialty = ?`, [specialty]);
    }
    // 获取设备及其所有参数
    getDeviceWithParameters(deviceId) {
        const device = this.get(`SELECT * FROM devices WHERE id = ?`, [deviceId]);
        if (!device)
            return null;
        return {
            device,
            namingParameters: this.all(`SELECT * FROM naming_parameters WHERE device_id = ?`, [deviceId]),
            positionParameters: this.all(`SELECT * FROM position_parameters WHERE device_id = ?`, [deviceId]),
            baseParameters: this.all(`SELECT * FROM base_parameters WHERE device_id = ?`, [deviceId])
        };
    }
    // 更新设备
    updateDevice(deviceId, updates) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const updateFields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
        const values = Object.values(updates);
        const result = this.run(`
      UPDATE devices 
      SET ${updateFields}, updated_at = ?
      WHERE id = ?
    `, [...values, now, deviceId]);
        if (result.changes > 0) {
            return this.get(`SELECT * FROM devices WHERE id = ?`, [deviceId]);
        }
        return null;
    }
    // 新增：清空所有主要表
    clearDatabase() {
        this.ensureInitialized();
        this.exec(`
      DELETE FROM devices;
      DELETE FROM naming_parameters;
      DELETE FROM position_parameters;
      DELETE FROM base_parameters;
    `);
    }
    // 新增：获取仪表板统计信息
    getDashboardStats() {
        const stmt = this.db.prepare("SELECT specialty, COUNT(*) as count FROM devices GROUP BY specialty ORDER BY count DESC");
        return stmt.all();
    }
    // 计量单位相关方法
    insertMeasurementUnit(unit) {
        const now = new Date().toISOString();
        const stmt = this.db.prepare(`
      INSERT OR IGNORE INTO measurement_units (
        category_name, unit_name, unit_symbol, unit_type,
        conversion_factor, base_unit, description, is_active,
        sort_order, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
        const result = stmt.run(unit.category_name, unit.unit_name, unit.unit_symbol || unit.unit_name, unit.unit_type || '常用单位', unit.conversion_factor || 1.0, unit.base_unit || unit.unit_name, unit.description || '', unit.is_active !== false ? 1 : 0, unit.sort_order || 0, now, now);
        return {
            id: result.lastInsertRowid,
            ...unit,
            unit_symbol: unit.unit_symbol || unit.unit_name,
            unit_type: unit.unit_type || '常用单位',
            conversion_factor: unit.conversion_factor || 1.0,
            base_unit: unit.base_unit || unit.unit_name,
            description: unit.description || '',
            is_active: unit.is_active !== false,
            sort_order: unit.sort_order || 0,
            created_at: now,
            updated_at: now
        };
    }
    getAllMeasurementUnits() {
        this.ensureInitialized();
        return this.all('SELECT * FROM measurement_units WHERE is_active = 1 ORDER BY category_name, sort_order, unit_name');
    }
    getMeasurementUnitsByCategory(category) {
        const stmt = this.db.prepare('SELECT * FROM measurement_units WHERE category_name = ? AND is_active = 1 ORDER BY sort_order, unit_name');
        return stmt.all(category);
    }
    getMeasurementUnitById(id) {
        const stmt = this.db.prepare('SELECT * FROM measurement_units WHERE id = ?');
        return stmt.get(id);
    }
    updateMeasurementUnit(id, updates) {
        const now = new Date().toISOString();
        const updateFields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
        const values = Object.values(updates);
        const stmt = this.db.prepare(`
      UPDATE measurement_units
      SET ${updateFields}, updated_at = ?
      WHERE id = ?
    `);
        return stmt.run(...values, now, id);
    }
    deleteMeasurementUnit(id) {
        const stmt = this.db.prepare('DELETE FROM measurement_units WHERE id = ?');
        return stmt.run(id);
    }
    clearMeasurementUnits() {
        const stmt = this.db.prepare('DELETE FROM measurement_units');
        const result = stmt.run();
        // 重置自增ID
        this.db.prepare("DELETE FROM sqlite_sequence WHERE name='measurement_units'").run();
        return result;
    }
    // 获取计量单位分类列表
    getMeasurementUnitCategories() {
        this.ensureInitialized();
        return this.all('SELECT DISTINCT category_name FROM measurement_units WHERE is_active = 1 ORDER BY category_name');
    }
    // 插入命名参数
    insertNamingParameter(param) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      INSERT INTO naming_parameters (device_id, sequence, parameter_name, parameter_value, data_type, example_description, measurement_class, data_source, is_required, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
            param.device_id,
            param.sequence || "",
            param.parameter_name,
            param.parameter_value || "",
            param.data_type,
            param.example_description,
            param.measurement_class,
            param.data_source,
            param.is_required ? 1 : 0,
            now,
            now
        ]);
        return {
            ...param,
            id: result.lastInsertRowid,
            created_at: now,
            updated_at: now
        };
    }
    // 插入位置参数
    insertPositionParameter(param) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      INSERT INTO position_parameters (device_id, sequence, parameter_name, parameter_value, data_type, example_description, measurement_class, data_source, is_required, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
            param.device_id,
            param.sequence || "",
            param.parameter_name,
            param.parameter_value || "",
            param.data_type,
            param.example_description,
            param.measurement_class,
            param.data_source,
            param.is_required ? 1 : 0,
            now,
            now
        ]);
        return {
            ...param,
            id: result.lastInsertRowid,
            created_at: now,
            updated_at: now
        };
    }
    // 插入基础参数
    insertBaseParameter(param) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      INSERT INTO base_parameters (device_id, sequence, parameter_name, example_description, data_type, measurement_class, data_source, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
            param.device_id,
            param.sequence || "",
            param.parameter_name,
            param.example_description || "",
            param.data_type,
            param.measurement_class || "",
            param.data_source || "",
            now,
            now
        ]);
        return {
            ...param,
            id: result.lastInsertRowid,
            created_at: now,
            updated_at: now
        };
    }
    // 查询命名参数
    getNamingParameters(deviceId) {
        this.ensureInitialized();
        return this.all(`SELECT * FROM naming_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 查询位置参数
    getPositionParameters(deviceId) {
        this.ensureInitialized();
        return this.all(`SELECT * FROM position_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 查询基础参数
    getBaseParameters(deviceId) {
        this.ensureInitialized();
        return this.all(`SELECT * FROM base_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 删除命名参数
    deleteNamingParameters(deviceId) {
        this.ensureInitialized();
        this.run(`DELETE FROM naming_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 删除位置参数
    deletePositionParameters(deviceId) {
        this.ensureInitialized();
        this.run(`DELETE FROM position_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 删除基础参数
    deleteBaseParameters(deviceId) {
        this.ensureInitialized();
        this.run(`DELETE FROM base_parameters WHERE device_id = ?`, [deviceId]);
    }
    // 删除设备（包括相关参数）
    deleteDevice(deviceId) {
        this.ensureInitialized();
        console.log(`[SQL] 开始删除设备: ${deviceId}`);
        // 通过device_id查找设备
        const device = this.getDeviceByDeviceId(deviceId);
        if (!device || !device.id) {
            console.log(`[SQL] 设备 ${deviceId} 不存在`);
            return false;
        }
        const internalId = device.id;
        // 删除相关参数
        console.log(`[SQL] DELETE FROM naming_parameters WHERE device_id = ${internalId}`);
        this.run(`DELETE FROM naming_parameters WHERE device_id = ?`, [internalId]);
        console.log(`[SQL] DELETE FROM position_parameters WHERE device_id = ${internalId}`);
        this.run(`DELETE FROM position_parameters WHERE device_id = ?`, [internalId]);
        console.log(`[SQL] DELETE FROM base_parameters WHERE device_id = ${internalId}`);
        this.run(`DELETE FROM base_parameters WHERE device_id = ?`, [internalId]);
        // 删除设备本身
        console.log(`[SQL] DELETE FROM devices WHERE id = ${internalId}`);
        const result = this.run(`DELETE FROM devices WHERE id = ?`, [internalId]);
        const success = result.changes > 0;
        console.log(`[SQL] 设备删除${success ? '成功' : '失败'}，影响行数: ${result.changes}`);
        return success;
    }
    // 更新命名参数值
    updateNamingParameterValue(parameterId, parameterValue) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      UPDATE naming_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
        if (result.changes > 0) {
            return this.get(`SELECT * FROM naming_parameters WHERE id = ?`, [parameterId]);
        }
        return null;
    }
    // 更新位置参数值
    updatePositionParameterValue(parameterId, parameterValue) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      UPDATE position_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
        if (result.changes > 0) {
            return this.get(`SELECT * FROM position_parameters WHERE id = ?`, [parameterId]);
        }
        return null;
    }
    // 更新基础参数值
    updateBaseParameterValue(parameterId, parameterValue) {
        this.ensureInitialized();
        const now = new Date().toISOString();
        const result = this.run(`
      UPDATE base_parameters 
      SET parameter_value = ?, updated_at = ?
      WHERE id = ?
    `, [parameterValue, now, parameterId]);
        if (result.changes > 0) {
            return this.get(`SELECT * FROM base_parameters WHERE id = ?`, [parameterId]);
        }
        return null;
    }
    prepare(sql) {
        this.ensureInitialized();
        return this.db.prepare(sql);
    }
    transaction(fn) {
        this.ensureInitialized();
        return this.db.transaction(fn);
    }
}
exports.SqliteDatabase = SqliteDatabase;
let dbInstance = null;
function getDbInstance() {
    if (!dbInstance) {
        dbInstance = new SqliteDatabase();
    }
    return dbInstance;
}
